IICSでGoogle スプレッドシートに接続してみる

Informatica Intelligent Cloud Services（IICS）のコネクタを使用して、 Google スプレッドシート（Google Sheets）に接続する。 Googleのリフレッシュトークンを使用する。

「カスタマーストーリー」を支えるツール特集

#Google APIs

#Informatica Intelligent Cloud Services

#Cloud Data Integration

#Google Sheets

#インフォマティカ

ikeda

2020.12.11

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

本エントリはクラスメソッドインフォマティカ Advent Calendar 2020のエントリです。

データアナリティクス事業本部、池田です。アドベントカレンダー11日目です！
Informatica Intelligent Cloud Services（以下IICS）にはいろいろなコネクタがありますが、今回はGoogle スプレッドシート（Google Sheets、以下スプレッドシート）に接続してみます。

【 Google Sheets Connector Overview 】

補足！！！

スプレッドシートのコネクタでの接続が目的なので、処理としては単純に Amazon S3 に吐き出すだけにします。

本当にスプレッドシートをS3に吐き出したいだけなら、もちろんIICSを使うまでもなく、例えば↓のような Google Apps Script（GAS）のライブラリを使うなどの方がたぶん楽です。

GoogleスプレッドシートのデータをS3へCSVとして保存する

Google Apps Scriptを利用してGoogleスプレッドシートのデータをS3へJSONとして保存する

また、スプレッドシート→ BigQuery の連携ができるので、IICSで既に使っているような場合は、 BigQueryを経由するのも可能かもしれません。（気が向いたら試してブログにします。）

スプレッドシート->BigQuery->データポータルでSQLでのデータ加工処理が必要なレポート作成を試してみる

↑一度きりのインポートのようなものではなく、スプレッドシートを変更するとBigQuery側も変更されました。（すごい！）

IICS(CDI)でRedshiftとBigQueryを行き来してみる

っていう前置きを踏まえて次章から、スプレッドシート側の準備、IICSでの接続、マッピング機能でのS3出力、と進めていきます。

スプレッドシート（GCP）側の準備（リフレッシュトークンの取得）

スプレッドシートの接続の作成に必要になるリフレッシュトークンを作成します。
スプレッドシートのコネクタは Google Sheets API を使用しており、このAPIを使うために必要になるのがリフレッシュトークンです。けっこうしんどい作業です。以下、ガイドを基に私がやった作業をざっと掲載します。

作業としては、コネクタのガイドの、 Administration of Google Sheets Connector と Generating OAuth 2.0 access tokens を行います。

クライアントIDとクライアントシークレット

Administration of Google Sheets Connector では、GoogleのクライアントIDとクライアントシークレットの取得までを行います。

GCPのコンソールから、Google Sheets APIを有効化します。

OAuth クライアント IDを作成します。

ただし、初めて作成する場合はOAuth 同意画面の設定が求められます。

（今回の用途の範囲に限って言えば、自分が許可する時の画面への影響だけなので、あまり気にしなくて良い部分ではないかと思います。たぶん。）

設定後にOAuth クライアント IDが作成できるようになります。

↑IICSのガイドでは特に触れていないようでしたが、私の場合はリダイレクトURIが後の手順で必要になりました。 http://localhost を設定しました。

作成が完了すると、目当てのクライアントIDとクライアントシークレットを含むJSONファイルがダウンロードできるようになります。

リフレッシュトークン

Generating OAuth 2.0 access tokens では、Googleのリフレッシュトークンを取得します。

Chromeで↓のURLにつなぎます。前節のクライアントIDとリダイレクトURIを使います。
https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=auto&client_id={クライアントID}&response_type=code&scope=https://www.googleapis.com/auth/spreadsheets&redirect_uri={リダイレクトURI}
（このURLの scope により、スプレッドシートのAPI使用の許可が求められます。）

表示される画面（←OAuth 同意画面の設定がここで効いてきます）で許可をしていくと、リダイレクトされます。そのURLのパラメータの code が認可コードです。
http://localhost/?code={認可コード}&scope=https://www.googleapis.com/auth/spreadsheets

その認可コードとクライアントID、クライアントシークレットを使ってPOSTリクエストを発行し、 リフレッシュトークンを取得します。
↓私はChromeのデベロッパーツール（F12）でJavaScript動かしてリクエストしました。

fetch('https://accounts.google.com/o/oauth2/token', {
  method: 'POST',
  body: JSON.stringify({
   "grant_type": "authorization_code",
   "code":"{認可コード}",
   "client_id":"{クライアントID}",
   "client_secret":"{クライアントシークレット}",
   "redirect_uri":"http://localhost"
  })
})
.then(response => response.json())
.then(data => {
  console.log('Success:', data);
});

このレスポンスの中に含まれる refresh_token が、念願のリフレッシュトークンです！！！

※
ここまででリフレッシュトークンが取得できたので、 IICSのガイドの『Step 3. Refreshing an access token』（アクセストークン）は、IICSで接続するだけであれば不要だと思います。
また、執筆時点のガイドではgrant_typeにまた authorization_code を指定してリクエストしていますが、正しくは refresh_token （ガイドだと画像の方が正しい）だと思われます。

使用するスプレッドシート

今回使うスプレッドシートは↓こんな感じです。

IMPORTXML 関数を使って、弊社ブログのページをスクレイピングしています。

接続してみる

前章長かったですね…ここからが本題です。

コネクタの有効化

「アドオンコネクタ」からコネクタを有効化します。

接続の作成

Google Sheets connection properties を参考に接続を作成します。

ランタイム環境 は準備しておいたSecure Agentを指定。 Hosted Agent ではダメでした…
ClientId と ClientSecret は前章でさんざん出てきたやつです。
RefreshTokenForSheet は前章の最後に取得できたリフレッシュトークン。
SpreadSheetId は接続したいスプレッドシートのURLから取得できます。
今回のスプレッドシートは1行目がヘッダーなので、 HeaderPresent をチェック。
（ InitialColumnRange で読込みの開始範囲が指定できるのかと思ったのですが、いろいろな値を試しましたが、私では効かせられませんでした……）